---- RackNo in VoucherDetail table Field Name Change To 	ManzalID

BEGIN TRANSACTION
GO
ALTER TABLE dbo.Voucher ADD
	StoreLot varchar(15) NULL
GO
COMMIT

USE [BSC]
GO
/****** Object:  Table [dbo].[Manzal]    Script Date: 12/08/2019 11:32:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Manzal](
	[ManzalID] [tinyint] IDENTITY(1,1) NOT NULL,
	[ManzalCode] [smallint] NOT NULL,
	[ManzalName] [varchar](25) NOT NULL,
	[ManzalNameUrdu] [varchar](25) NOT NULL,
	[Discontinue] [bit] NOT NULL CONSTRAINT [DF_Manzal_Discontinue]  DEFAULT ((0)),
 CONSTRAINT [PK_Manzal] PRIMARY KEY CLUSTERED 
(
	[ManzalID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

-----------------------------------------------------------

USE [BSC]
GO
/****** Object:  View [dbo].[VW_Vouchers]    Script Date: 12/08/2019 16:42:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[VW_Vouchers]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, 
                      dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.CustomerName, 
                      dbo.Accounts.Remarks AS CustomerAddress, dbo.Accounts.CellNo, dbo.Accounts.Phone, dbo.Accounts.NameUrdu, dbo.Accounts.AreaID, dbo.Voucher.ReadOnly, 
                      dbo.Voucher.Amount AS SumAmount, dbo.Voucher.Expense, dbo.Voucher.Discount, dbo.Voucher.TotalAmount, dbo.Voucher.NetAmount, dbo.Voucher.Cash, 
                      dbo.Voucher.GroupID AS SubPartyID, ISNULL(dbo.Groups.GroupName, '.') AS SubPartyName, dbo.VoucherDetails.VoucherDetailID, dbo.VoucherDetails.AccountNo, 
                      dbo.Accounts.AccountTitle, dbo.VoucherDetails.Remarks, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE - 1 * dbo.VoucherDetails.Amount END AS Amount, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Cr') THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, 
                      dbo.VoucherDetails.DrOrCr, dbo.Accounts.ParentAccount, dbo.Voucher.BankAccountID, dbo.Voucher.StoreID, dbo.Voucher.VehicleNo, dbo.Voucher.LoginID, 
                      dbo.Employees.EmployeeName, dbo.Voucher.HostName, dbo.Voucher.EntryDateTime, Accounts_2.AccountTitle AS BankAccountTitle, 
                      dbo.VoucherDetails.Amount AS ActualAmount, dbo.Voucher.ModifyID, Employees_1.EmployeeName AS ModifyName, dbo.Voucher.ModifyHostName, 
                      dbo.Voucher.ModifyDateTime, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, dbo.vw_Items.ItemNameUrdu, dbo.VoucherDetails.Qty, 
                      dbo.VoucherDetails.Rate, dbo.vw_Items.GroupID, dbo.vw_Items.GroupName, dbo.vw_Items.GroupNameUrdu, dbo.VoucherDetails.RackID, dbo.Rack.RackCode, 
                      dbo.Rack.RackName, dbo.Rack.RackNameUrdu, dbo.VoucherDetails.ManzalID, dbo.Manzal.ManzalCode, dbo.Manzal.ManzalName, dbo.Manzal.ManzalNameUrdu, 
                      dbo.VoucherDetails.RoomID, dbo.Room.RoomCode, dbo.Room.RoomName, dbo.Room.RoomNameUrdu, dbo.VoucherDetails.LotNo, dbo.VoucherDetails.Marka
FROM         dbo.Manzal RIGHT OUTER JOIN
                      dbo.VoucherDetails ON dbo.Manzal.ManzalID = dbo.VoucherDetails.ManzalID LEFT OUTER JOIN
                      dbo.Rack ON dbo.VoucherDetails.RackID = dbo.Rack.RackID LEFT OUTER JOIN
                      dbo.Room ON dbo.VoucherDetails.RoomID = dbo.Room.RoomID LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo LEFT OUTER JOIN
                      dbo.Employees AS Employees_1 RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_2 RIGHT OUTER JOIN
                      dbo.Groups RIGHT OUTER JOIN
                      dbo.Voucher ON dbo.Groups.GroupID = dbo.Voucher.GroupID ON Accounts_2.AccountNo = dbo.Voucher.BankAccountID ON 
                      Employees_1.EmployeeID = dbo.Voucher.ModifyID LEFT OUTER JOIN
                      dbo.Employees ON dbo.Voucher.LoginID = dbo.Employees.EmployeeID ON dbo.VoucherDetails.VoucherID = dbo.Voucher.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID

-------------------------------------------

USE [BSC]
GO
/****** Object:  View [dbo].[vw_VouchersDetail]    Script Date: 12/08/2019 16:42:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_VouchersDetail]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherNo, dbo.Voucher.VoucherDate, dbo.Voucher.GroupID, dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, 
                      SessionInfo_1.SessionTitle, dbo.VoucherDetails.Remarks, dbo.VoucherDetails.Amount AS Debit, 0 AS Credit, dbo.VoucherDetails.DrOrCr, 
                      dbo.VoucherDetails.VoucherDetailID, dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.SessionID, dbo.Accounts.ParentAccount, dbo.Voucher.BankAccountID, 
                      ISNULL(dbo.Voucher.CustomerName, '') AS CustomerName, dbo.VoucherDetails.ItemID, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Qty AS InQty, 0 AS OutQty, 
                      dbo.VoucherDetails.Rate, ISNULL(dbo.Groups.GroupName, '.') AS SubPartyName, dbo.Voucher.Amount AS SumAmount, dbo.Voucher.Expense, dbo.Voucher.Discount, 
                      dbo.Voucher.TotalAmount, dbo.Voucher.NetAmount, dbo.Voucher.Cash, dbo.Voucher.StoreID, dbo.Voucher.VehicleNo, dbo.Accounts.NameUrdu, 
                      dbo.VoucherDetails.RoomID, dbo.Room.RoomCode, dbo.Room.RoomName, dbo.Room.RoomNameUrdu, dbo.VoucherDetails.RackID, dbo.Rack.RackCode, 
                      dbo.Rack.RackName, dbo.Rack.RackNameUrdu, dbo.VoucherDetails.ManzalID, dbo.VoucherDetails.LotNo, dbo.VoucherDetails.Marka
FROM         dbo.Rack RIGHT OUTER JOIN
                      dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.Room ON dbo.VoucherDetails.RoomID = dbo.Room.RoomID ON dbo.Rack.RackID = dbo.VoucherDetails.RackID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo LEFT OUTER JOIN
                      dbo.Groups RIGHT OUTER JOIN
                      dbo.Voucher ON dbo.Groups.GroupID = dbo.Voucher.GroupID ON dbo.VoucherDetails.VoucherID = dbo.Voucher.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo AS SessionInfo_1 ON dbo.Voucher.SessionID = SessionInfo_1.SessionID
WHERE     (dbo.VoucherDetails.DrOrCr = N'Dr')
UNION ALL
SELECT     VoucherDetails_1.VoucherID, Voucher_1.VoucherNo, Voucher_1.VoucherDate, Voucher_1.GroupID, VoucherDetails_1.AccountNo, Accounts_1.AccountTitle, 
                      SessionInfo_1.SessionTitle, VoucherDetails_1.Remarks, 0 AS Debit, VoucherDetails_1.Amount AS Credit, VoucherDetails_1.DrOrCr, VoucherDetails_1.VoucherDetailID, 
                      Voucher_1.Remarks AS MasterRemarks, Voucher_1.SessionID, Accounts_1.ParentAccount, Voucher_1.BankAccountID, ISNULL(Voucher_1.CustomerName, '') 
                      AS CustomerName, VoucherDetails_1.ItemID, VoucherDetails_1.Qty, 0 AS InQty, VoucherDetails_1.Qty AS OutQty, VoucherDetails_1.Rate, 
                      ISNULL(Groups_1.GroupName, '.') AS SubPartyName, Voucher_1.Amount AS SumAmount, Voucher_1.Expense, Voucher_1.Discount, Voucher_1.TotalAmount, 
                      Voucher_1.NetAmount, Voucher_1.Cash, Voucher_1.StoreID, Voucher_1.VehicleNo, Accounts_1.NameUrdu, VoucherDetails_1.RoomID, Room_1.RoomCode, 
                      Room_1.RoomName, Room_1.RoomNameUrdu, VoucherDetails_1.RackID, Rack_1.RackCode, Rack_1.RackName, Rack_1.RackNameUrdu, 
                      VoucherDetails_1.ManzalID, VoucherDetails_1.LotNo, VoucherDetails_1.Marka
FROM         dbo.Rack AS Rack_1 RIGHT OUTER JOIN
                      dbo.VoucherDetails AS VoucherDetails_1 LEFT OUTER JOIN
                      dbo.Room AS Room_1 ON VoucherDetails_1.RoomID = Room_1.RoomID ON Rack_1.RackID = VoucherDetails_1.RackID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON VoucherDetails_1.AccountNo = Accounts_1.AccountNo LEFT OUTER JOIN
                      dbo.Groups AS Groups_1 RIGHT OUTER JOIN
                      dbo.Voucher AS Voucher_1 ON Groups_1.GroupID = Voucher_1.GroupID ON VoucherDetails_1.VoucherID = Voucher_1.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo AS SessionInfo_1 ON Voucher_1.SessionID = SessionInfo_1.SessionID
WHERE     (VoucherDetails_1.DrOrCr = N'Cr')